create database  StudentAttendanceCheckin
go
use StudentAttendanceCheckin
go
-- PHAN VUNG TAO CAC BANG --
create table Student
(
	StudentID varchar(6) not null,-- ma sinh vien vd A04879
	Name nvarchar(50) check(len(Name) >0 and len(Name)<=50) not null,-- ten sinh vien
	Gender bit default 1,-- gioi tinh 0 nam 1 nu
	BrithDay datetime, -- ngay sinh
	Addresss nvarchar(150) check(len(Addresss) <= 150),-- dia chi
	Phone varchar(15),-- dien thoai
	Email varchar(100), -- hom thu
	CurrentClass int identity(1,1), -- lop hien tai dang hoc
	StudentStatus int, -- trang thai sinh vien
	Descriptions nvarchar(max), -- mo ta sinh vien neu co
	constraint PK_Student primary key (StudentID) -- khoa chinh
)
go
create table Branch
(
	BranchID int identity(1,1) not null,-- ma dia diem
	BranchName nvarchar(50) not null, -- ten dia diem
	constraint PK_Area primary key (BranchID)
)
go
create table TrainingProgrammeDetails
(
	TrainingProgramme varchar(30),-- ten chuong trinh dao tao
	StatusTraining bit default 1,
	DefaultTraining tinyint default 0,
	constraint PK_Class_TrainingProgrammeDetails primary key (TrainingProgramme)
)
go
create table Teacher
(
	TeacherID int identity(1,1) not null,
	TeacherName nvarchar(30) check(len(TeacherName) <= 30 and len(TeacherName) > 0) not null, --ten giao vien chu nhiem
	Active bit,
	constraint PK_Teacher primary key (TeacherID)
)
go
create table Class
(
	ClassID int identity(1,1) not null, -- ma lop
	ClassName varchar(6) check(len(ClassName)>0 and len(ClassName)<=6) not null, -- ten lop
	TeacherID int not null, -- ma giao vien
	TrainingProgramme varchar(30) check(len(TrainingProgramme) <= 30 and len(TrainingProgramme) > 0),-- ten chuong trinh dao tao
	BranchID int, -- ma dia diem
	TimeSlot varchar(40) check(len(TimeSlot) <= 40 and len(TimeSlot) > 0),-- thoi gian hoc
	StartDay datetime, -- ngay vao hoc
	EndDay datetime, -- ngay ket thuc
	ClassStatus bit,-- 0 la khong hoat dong 1 la dang hoat dong 2 la sap hoat dong
	constraint PK_Class primary key (ClassID),
	constraint FK_Class_AreaID foreign key (BranchID) references Branch(BranchID)
)
go
create table GroupUser
(
	GroupUserID int identity(1,1) not null, -- ma nhom
	GroupName nvarchar(50) not null, -- ten gorup
	constraint PK_GroupUser primary key (GroupUserID)
)
go
create table Users
(
	UsersID varchar(30) not null, -- ma tai khoan
	UsersName nvarchar(50) not null, -- ten tai khoan
	GroupUserID int not null, -- nhom tai khoan co 2 nhom faculties va staffs
	constraint PK_Users primary key (UsersID),
	constraint FK_Users_GroupUser foreign key (GroupUserID) references GroupUser(GroupUserID)
)
go
create table Permission
(
	PermissionID int identity(1,1) not null,
	GroupUserID int not null,
	Active varchar(100),
	constraint PK_Permission primary key (PermissionID)
)
go 
create table Subjects
(
	SubjectCode varchar(10), -- ma mon hoc
	SubjectName varchar(40), -- ten mon hoc
	SubjectTotal int, -- tong so tiet hoc
	WTest bit default 1, -- ly thuyet
	PTest bit default 1, -- thuc hanh
	constraint PK_Subject_SubjectCode primary key (SubjectCode)
)
go
create table Attendants
(
	AttendantsID int identity(1,1) not null, -- ma diem danh
	UsersID varchar(30) not null, -- giao vien diem danh
	ClassID int not null,	-- lop diem danh
	SubjectCode varchar(10), -- diem danh mon
	AttendantsDate datetime default(getdate()), -- ngay diem danh
	constraint PK_Attendants primary key (AttendantsID)
)
go
create table AttendantsDetails
(
	AttendantsDetailsID int identity(1,1) not null, -- ma diem danh chi tiet
	AttendantsID int not null, -- ma diem danh
	StudentID varchar(6) not null, -- ma sinh vien
	AttendantStatus int, -- 0 nghi hoc, 1 nghi phep, 2 di hoc
	Note nvarchar(max), -- ghi chu neu co
	constraint PK_AttendantsDetails primary key (AttendantsDetailsID)
)
go
create table AddStudentClass
(
	AddStudentClassID int identity(1,1) not null, -- ma xep lop
	StudentID varchar(6) not null, -- ma sinh vien
	ClassID int not null, -- ma lop
	ClassFrist int, -- lop cu
	ClassMove int, -- lop moi dich chuyen
	Note nvarchar(max), -- ghi chu
	constraint PK_AddStudentClass primary key (AddStudentClassID)
)
-- PHAN TAO CAC PROC --
--tao proc kiem tra ten ton tai va add du lieu vao data
go
create proc Add_Branch
@BranchName nvarchar(50)
as
--check ton tai ten
begin
	if exists(select Branch.BranchName from Branch where Branch.BranchName = @BranchName)
		rollback transaction
	else
		insert into Branch(BranchName) values (@BranchName)
end
